package com.from.menlo.park.db;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;

import com.from.menlo.park.data.Entry;
import com.from.menlo.park.data.Location;
import com.from.menlo.park.data.Photo;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.BitmapFactory;

public class DbUtile extends SQLiteOpenHelper {

	final protected static String DATABASE_NAME="ediary";

	public DbUtile(Context context) {
		super(context, DATABASE_NAME, null, 4);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("DROP TABLE images;");
		db.execSQL("CREATE TABLE images (id INTEGER PRIMARY KEY AUTOINCREMENT, entry_id INTEGER, image BLOB);");
		db.execSQL("DROP TABLE entries;");
		db.execSQL("CREATE TABLE entries (id INTEGER PRIMARY KEY AUTOINCREMENT,  time INTEGER NOT NULL,, lat double, lon double,"
				+" address TEXT, mood TEXT, tag TEXT, note TEXT, has_photo INTEGER);");
//		db.execSQL("CREATE TABLE entries (id INTEGER PRIMARY KEY AUTOINCREMENT,  time TIMESTAMP NOT NULL DEFAULT current_timestamp,, lat double, lon double,"
//				+" address TEXT, mood TEXT, tag TEXT, note TEXT);");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if (oldVersion >= newVersion)
			return;

	    onCreate(db);
	}
	
	public Entry getEntryById(int entryId){
		 SQLiteDatabase db =  getReadableDatabase();
		 // Cursor cursor = db.rawQuery("SELECT id, (strftime('%s', time) * 1000) AS time, lat, lon, address, mood, tag, note FROM entries ;", null);
		  Cursor cursor = db.rawQuery("SELECT id,  time, lat, lon, address, mood, tag, note, has_photo FROM entries ;", null);

          Entry entry = null;
		  if (cursor.getCount()>0) {
			  cursor.moveToNext();  
			  entry =  getEntry(db, cursor, true); 
		  }
		  cursor.close();
		  return entry;
	}
	
	public List<Entry> getEntryByDateRange(int start,  int end, boolean fetchPhoto){
		 List<Entry> entries = new ArrayList<Entry>();
		 SQLiteDatabase db =  getReadableDatabase();
		 // Cursor cursor = db.rawQuery("SELECT id, (strftime('%s', time) * 1000) AS time, lat, lon, address, mood, tag, note FROM entries ;", null);
		 Cursor cursor = db.rawQuery("SELECT id,  time, lat, lon, address, mood, tag, note, has_photo FROM entries where time >=" + start + " and time <=" + end + " sort by time ;", null);
 		 while (cursor.getCount()>0) {
			  cursor.moveToNext(); 
			  entries.add( getEntry(db, cursor, fetchPhoto)); 
		  }
		  cursor.close();
		  return entries;
	}
	
	private Entry getEntry(SQLiteDatabase db, Cursor cursor, boolean fetchPhoto){
		 Entry entry = new Entry();
		  entry.setId( cursor.getInt(cursor.getColumnIndex("id")));
		  long timestamp = cursor.getInt(cursor.getColumnIndex("time")) * 1000;
		  entry.setTime( new Date(timestamp));
		  Location location = new Location(
				  cursor.getDouble(cursor.getColumnIndex("lat")),
				  cursor.getDouble(cursor.getColumnIndex("lon")),
				  cursor.getString(cursor.getColumnIndex("address"))
				  );
		  entry.setLocation(location);
		  entry.setNote( cursor.getString(cursor.getColumnIndex("note")));
		  entry.setMoodStr( cursor.getString(cursor.getColumnIndex("mood")));
		  entry.setTagStr( cursor.getString(cursor.getColumnIndex("tag")));
		  entry.setHasPhotos( cursor.getInt(cursor.getColumnIndex("has_photo") ) != 0 );
		//  cursor.close();
		  if ( fetchPhoto && entry.isHasPhotos() ) {
		      List<Photo> photos = getPhotoByEntryId( entry.getId()  ); 
		      if( photos.size() > 0 ){
			      entry.setPhotos( photos.toArray(new Photo[photos.size()]))  ;
		      }
		  }
		  return entry;
	}
	
	public List<Photo> getPhotoByEntryId(long entryId){
		 SQLiteDatabase db =  getReadableDatabase();
		 Cursor cursor = db.rawQuery("SELECT * FROM images where entry_id = " +  entryId + ";", null);
		  List<Photo> photos = new ArrayList<Photo>();
		  while (cursor.getCount()>0) {
			  cursor.moveToNext();
			  photos.add( new Photo(
					  cursor.getInt(cursor.getColumnIndex("id")),
					  cursor.getBlob(cursor.getColumnIndex("image")),
					  null,
					  cursor.getInt(cursor.getColumnIndex("entry_id"))
					  )); 
		  }
		  cursor.close();
		  return photos;
	}
	
	
	public void deleteEntry(long entryId){
		SQLiteDatabase db =  getReadableDatabase();
		db.beginTransaction();
		 String deleteImages = "DELETE FROM images WHERE entry_id =?; ";
		 db.execSQL(deleteImages, new Long[]{entryId}); 
		 String deleteEntry = "DELETE FROM entries WHERE id =?; ";
		 db.execSQL(deleteEntry, new Long[]{entryId}); 
		 db.setTransactionSuccessful();
		 db.endTransaction(); 
	}
	
	public void saveEntry(Entry entry){
		 SQLiteDatabase db =  getReadableDatabase();
		 db.beginTransaction();
		 String saveEntrySql = "INSERT INTO entries(" +
		                    " time, lat, lon, address, mood, tag, note, has_photo) VALUES(?, ?, ?, ?, ?, ?, ?,?);";
		 db.execSQL(saveEntrySql, new Object[]{
				 entry.getTime() == null ? new Integer( (int) System.currentTimeMillis() / 1000 ) : 
					 new Integer( (int) entry.getTime().getTime() / 1000 ),
				 entry.getLocation() == null ? 0 : entry.getLocation().latitude,
				 entry.getLocation() == null ? 0 : entry.getLocation().longitude,
				 entry.getLocation() == null ? "" :entry.getLocation().address,
				 entry.getMoodStr(),
				 entry.getTagStr(),
				 entry.getNote(),
				 entry.isHasPhotos() || entry.getPhotos() != null ? 1 : 0});
		 
		 
		 Cursor cursor = db.rawQuery("SELECT last_insert_rowid() FROM EventType", null);
         if (cursor.getCount()>0) {
        	 cursor.moveToNext();
        	 long id = cursor.getLong(0);
        	 entry.setId(id);
        	 if ( entry.getPhotos() != null ){
        		 for( Photo p : entry.getPhotos() ){
        			 ContentValues values = new ContentValues();
     			     values.put("image", p.getData());
     			     values.put("entry_id", id);
     			     db.insert("images", null, values);
        		 }
        	 }
         }
         db.setTransactionSuccessful();
		 db.endTransaction();
	}
	 

}